<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<%!String name = null;
	Connection conn;
	Statement stmt;%>
<%!public Connection conn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加载Mysql数据驱动
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "198666");// 创建数据连接
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.close();
			} catch (SQLException s) {
				s.printStackTrace();
			}
		}
		return conn;
	}%>
<%!public List convertList(ResultSet rs) {
		List list = new ArrayList();
		try {
			ResultSetMetaData md = rs.getMetaData();
			int columnCount = md.getColumnCount(); //Map rowData;
			while (rs.next()) { //rowData = new HashMap(columnCount);
				Map rowData = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}%>
<%!public List enquire(String empno, String ename) {
		conn = conn();
		ResultSet enquireRs = null;
		List list = null;
		StringBuffer sb = new StringBuffer();
		int i = 0;
		sb.append("select * from emp");
		if (!((empno == null || empno.length() == 0)
				&& (ename == null || ename.length() == 0))) {
			sb.append(" where ");
			if (!(empno == null || empno.length() == 0)) {
				if (i != 0) {
					sb.append(" and ");
				}
				sb.append("empno like '%" + empno + "%'");
				i++;
			}
			if (!(ename == null || ename.length() == 0)) {
				if (i != 0) {
					sb.append(" and ");
				}
				sb.append("ename like '%" + ename + "%'");
				i++;
			}
		}
		sb.append(" order by hiredate desc;");
		//System.out.println(sb.toString());
		try {
			stmt = conn.createStatement();
			enquireRs = stmt.executeQuery(sb.toString());
			list = convertList(enquireRs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				enquireRs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}%>
<body>
	<%
		String empno = request.getParameter("empno");
		String ename = request.getParameter("ename");
		if (!(empno == null || ename == null)) {
			List list = enquire(empno, ename);
			if (list == null || list.size() == 0) {
	%>
	<h2>
		没有记录，点击<a href="enquire.jsp">这里</a>返回查询页。
	</h2>
	<%
		} else {
	%>
	<center>
		<table border="1">
			<tr>
				<td>雇员编号</td>
				<td>雇员姓名</td>
				<td>雇员工作</td>
				<td>雇用日期</td>
				<td>基本工资</td>
				<td>奖金</td>
			</tr>
			<%
				for (int i = 0; i < list.size(); i++) {
							Map m = (Map) list.get(i);
			%>
			<tr>
				<td><%=m.get("empno")%></td>
				<td><%=m.get("ename")%></td>
				<td><%=m.get("job")%></td>
				<td><%=m.get("hiredate")%></td>
				<td><%=m.get("sal")%></td>
				<td><%=m.get("comm")%></td>
			</tr>
			<%
				}
			%>
		</table>
	</center>
	<%
		}
		}
	%>

</body>
</html>